<!DOCTYPE html>
<html>
<!--
Copyright 2007 The Closure Library Authors. All Rights Reserved.

Use of this source code is governed by an Apache 2.0 License.
See the COPYING file for details.
-->
<!--
  Author: ajpalay@google.com (Andrew Palay)
-->
<head>
<title>Closure Unit Tests - goog.gears.Database</title>
<script src="../base.js"></script>
<script>
  goog.require('goog.gears.Database');
  goog.require('goog.testing.jsunit');
</script>
</head>
<body>
<script>

function isGearsAllowed() {
  return goog.gears.hasFactory() && goog.gears.getFactory().hasPermission;
}

var database;

function setUpPage() {
  if (isGearsAllowed()) {
    try {
      database = new goog.gears.Database('tester', 'database-test');
    } catch (e) {
      debug('Could not create the database');
    }
  }
  setUpPageStatus = 'complete'; // jsunit magic
}

function setUp() {
  if (!database) {
    return;
  }
  database.execute('DROP TABLE IF EXISTS Test');
  database.execute('CREATE TABLE IF NOT EXISTS Test (x TEXT, y TEXT)');

  database.execute('INSERT INTO Test VALUES (?, ?)', 'a', 'alpha');
  database.execute('INSERT INTO Test VALUES (?, ?)', 'b', 'beta');
  database.execute('INSERT INTO Test VALUES (?, ?)', 'g', 'gamma');
}

function tearDown() {
  if (database) {
    goog.events.removeAll(database);

    // Rollback to clean up transaction state.
    while (database.isInTransaction()) {
      database.rollback();
    }
  }
}

function testCreateDatabase() {
  if (!database) {
    return;
  }
  assertNotNull('Could not create the database', database);
}

function testCreateTable() {
  if (!database) {
    return;
  }

  var created = database.queryValue('SELECT 1 FROM SQLITE_MASTER ' +
      'WHERE TYPE=? AND NAME=?',
      'table',
      'Test') != null;
  assertTrue('Test table could not be created', created);
  assertEquals('Wrong number of rows after more insertions', 3,
               database.queryValue('SELECT COUNT(*) FROM Test'));
}

function testQueryValue() {
  if (!database) {
    return;
  }
  // testing retrieving a single value
  var val = database.queryValue('SELECT y FROM Test WHERE x=?', 'b');
  assertEquals('Wrong value returned by query', 'beta', val);

  // testing a query that returns nothing
  val = database.queryValue('SELECT y FROM Test WHERE x=?', 'd');
  assertNull('Should not have returned a value for the query', val);

  // testing that the query returns the first row of the query
  val = database.queryValue('SELECT y FROM Test');
  assertEquals('Wrong value returned by query', 'alpha', val);

  // testing that the query returns the first element of the row of the query
  val = database.queryValue('SELECT * FROM Test');
  assertEquals('Wrong value returned by query', 'a', val);

  // And the same with an array.

  // testing retrieving a single value
  val = database.queryValue('SELECT y FROM Test WHERE x=?', ['b']);
  assertEquals('Wrong value returned by query', 'beta', val);

  // testing a query that returns nothing
  val = database.queryValue('SELECT y FROM Test WHERE x=?', ['d']);
  assertNull('Should not have returned a value for the query', val);
}

function testQueryObject() {
  if (!database) {
    return;
  }
  // testing that the query returns the correct object
  var obj = database.queryObject('SELECT * FROM Test WHERE x=?', 'b');
  assertNotNull('Should have returned a value for the query', obj);
  assertEquals('Wrong value returned by query', 'b', obj['x']);
  assertEquals('Wrong value returned by query', 'beta', obj['y']);

  // testing that the query returns null when there is no match
  obj = database.queryValue('SELECT * FROM Test WHERE x=?', 'd');
  assertNull('Should not have returned a value for the query', obj);

  // testing that the query returns the first row when there are multiple
  // rows returned.
  obj = database.queryObject('SELECT * FROM Test');
  assertNotNull('Should have returned a value for the query', obj);
  assertEquals('Wrong value returned by query', 'a', obj['x']);
  assertEquals('Wrong value returned by query', 'alpha', obj['y']);

  // And the same with an array.

  // testing that the query returns the correct object
  obj = database.queryObject('SELECT * FROM Test WHERE x=?', ['b']);
  assertNotNull('Should have returned a value for the query', obj);
  assertEquals('Wrong value returned by query', 'b', obj['x']);
  assertEquals('Wrong value returned by query', 'beta', obj['y']);

  // testing that the query returns null when there is no match
  obj = database.queryValue('SELECT * FROM Test WHERE x=?', ['d']);
  assertNull('Should not have returned a value for the query', obj);
}

function testQueryValueArray() {
  if (!database) {
    return;
  }
  // testing selecting returning of multiple single values
  var arr = database.queryValueArray('SELECT y FROM Test');
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 3, arr.length);
  assertEquals('Wrong value returned by query', 'alpha', arr[0]);
  assertEquals('Wrong value returned by query', 'beta', arr[1]);
  assertEquals('Wrong value returned by query', 'gamma', arr[2]);

  // testing selecting returning of multiple single values when there
  // are no matches.
  arr = database.queryValueArray('SELECT y FROM Test WHERE x=?', 'd');
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 0, arr.length);

  // testing selecting returning of multiple single values even when
  // selecting multiple columns - should return the first column
  arr = database.queryValueArray('SELECT * FROM Test');
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 3, arr.length);
  assertEquals('Wrong value returned by query', 'a', arr[0]);
  assertEquals('Wrong value returned by query', 'b', arr[1]);
  assertEquals('Wrong value returned by query', 'g', arr[2]);

  // And the same with an array.

  // testing selecting returning of multiple single values when there
  // are no matches.
  arr = database.queryValueArray('SELECT y FROM Test WHERE x=?', ['d']);
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 0, arr.length);

}

function testQueryObjectArray() {
  if (!database) {
    return;
  }
  // testing selecting returning of array of objects
  var arr = database.queryObjectArray('SELECT * FROM Test');
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 3, arr.length);
  assertEquals('Wrong value returned by query', 'a', arr[0]['x']);
  assertEquals('Wrong value returned by query', 'alpha', arr[0]['y']);
  assertEquals('Wrong value returned by query', 'b', arr[1]['x']);
  assertEquals('Wrong value returned by query', 'beta', arr[1]['y']);
  assertEquals('Wrong value returned by query', 'g', arr[2]['x']);
  assertEquals('Wrong value returned by query', 'gamma', arr[2]['y']);

  // testing selecting returning of multiple objects when there
  // are no matches.
  arr = database.queryObjectArray('SELECT y FROM Test WHERE x=?', 'd');
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 0, arr.length);

  // And the same with an array.

  // testing selecting returning of multiple objects when there
  // are no matches.
  arr = database.queryObjectArray('SELECT y FROM Test WHERE x=?', ['d']);
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 0, arr.length);

}

function testQueryToArrays() {
  if (!database) {
    return;
  }
  // testing selecting returning of array of arrays
  var arr = database.queryArrays('SELECT * FROM Test');
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 3, arr.length);
  assertEquals('Wrong value returned by query', 'a', arr[0][0]);
  assertEquals('Wrong value returned by query', 'alpha', arr[0][1]);
  assertEquals('Wrong value returned by query', 'b', arr[1][0]);
  assertEquals('Wrong value returned by query', 'beta', arr[1][1]);
  assertEquals('Wrong value returned by query', 'g', arr[2][0]);
  assertEquals('Wrong value returned by query', 'gamma', arr[2][1]);

  arr = database.queryArrays('SELECT * FROM Test WHERE x=?', 'd');
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 0, arr.length);

  // And the same with an array.

  arr = database.queryArrays('SELECT * FROM Test WHERE x=?', ['d']);
  assertNotNull('Should have returned a value for the query', arr);
  assertEquals('Wrong value returned by query', 0, arr.length);
}

function testForEachValue() {
  if (!database) {
    return;
  }
  // testing the foreach value when there are matching values
  var vals = [];
  database.forEachValue('SELECT * FROM Test',
                        function(val, row, col, name) {
                          vals.push({val: val, row: row, col: col, name: name});
                          return true;
                        },
                        null);
  assertEquals('Wrong value returned by query', 6, vals.length);
  assertEquals('Wrong value returned by query', 'a', vals[0].val);
  assertEquals('Wrong value returned by query', 0, vals[0].row);
  assertEquals('Wrong value returned by query', 0, vals[0].col);
  assertEquals('Wrong value returned by query', 'x', vals[0].name);
  assertEquals('Wrong value returned by query', 'alpha', vals[1].val);
  assertEquals('Wrong value returned by query', 0, vals[1].row);
  assertEquals('Wrong value returned by query', 1, vals[1].col);
  assertEquals('Wrong value returned by query', 'y', vals[1].name);
  assertEquals('Wrong value returned by query', 'b', vals[2].val);
  assertEquals('Wrong value returned by query', 1, vals[2].row);
  assertEquals('Wrong value returned by query', 0, vals[2].col);
  assertEquals('Wrong value returned by query', 'x', vals[2].name);
  assertEquals('Wrong value returned by query', 'beta', vals[3].val);
  assertEquals('Wrong value returned by query', 1, vals[3].row);
  assertEquals('Wrong value returned by query', 1, vals[3].col);
  assertEquals('Wrong value returned by query', 'y', vals[3].name);
  assertEquals('Wrong value returned by query', 'g', vals[4].val);
  assertEquals('Wrong value returned by query', 2, vals[4].row);
  assertEquals('Wrong value returned by query', 0, vals[4].col);
  assertEquals('Wrong value returned by query', 'x', vals[4].name);
  assertEquals('Wrong value returned by query', 'gamma', vals[5].val);
  assertEquals('Wrong value returned by query', 2, vals[5].row);
  assertEquals('Wrong value returned by query', 1, vals[5].col);
  assertEquals('Wrong value returned by query', 'y', vals[5].name);

  vals = [];
  database.forEachValue('SELECT * FROM Test WHERE x=?',
                        function(val, row, col, name) {
                          vals.push({val: val, row: row, col: col, name: name});
                        },
                        null,
                        'd');
  assertEquals('Wrong value returned by query', 0, vals.length);

  // And the same with an array.

  vals = [];
  database.forEachValue('SELECT * FROM Test WHERE x=?',
                        function(val, row, col, name) {
                          vals.push({val: val, row: row, col: col, name: name});
                        },
                        null,
                        ['d']);
  assertEquals('Wrong value returned by query', 0, vals.length);

}

function testForEachRow() {
  if (!database) {
    return;
  }
  var vals = [];
  database.forEachRow('SELECT * FROM Test',
                      function(arr, row) {
                        vals.push({arr: arr, row: row});
                        return true;
                      },
                      null);
  assertEquals('Wrong value returned by query', 3, vals.length);
  assertEquals('Wrong value returned by query', 'a', vals[0].arr[0]);
  assertEquals('Wrong value returned by query', 'alpha', vals[0].arr[1]);
  assertEquals('Wrong value returned by query', 0, vals[0].row);
  assertEquals('Wrong value returned by query', 'b', vals[1].arr[0]);
  assertEquals('Wrong value returned by query', 'beta', vals[1].arr[1]);
  assertEquals('Wrong value returned by query', 1, vals[1].row);
  assertEquals('Wrong value returned by query', 'g', vals[2].arr[0]);
  assertEquals('Wrong value returned by query', 'gamma', vals[2].arr[1]);
  assertEquals('Wrong value returned by query', 2, vals[2].row);

  vals = [];
  database.forEachRow('SELECT * FROM Test WHERE x=?',
                      function(arr, row) {
                        vals.push({arr: arr, row: row});
                        return true;
                      },
                      null,
                      'd');
  assertEquals('Wrong value returned by query', 0, vals.length);

  // And the same with an array.

  vals = [];
  database.forEachRow('SELECT * FROM Test WHERE x=?',
                      function(arr, row) {
                        vals.push({arr: arr, row: row});
                        return true;
                      },
                      null,
                      ['d']);
  assertEquals('Wrong value returned by query', 0, vals.length);

}

function testMultipleBegins() {
  if (!database) {
    return;
  }
  var db1;
  var db2;
  try {
    db1 = new goog.gears.Database('tester', 'database-test');
    db2 = new goog.gears.Database('tester', 'database-test');
  } catch (e) {
    fail('Could not create the databases');
  }

  db1.begin();
  try {
    // This should time out as being locked.
    db2.begin();
    fail('Should not have been able to get past second begin transaction.');
  } catch (e) {
    assertFalse(
        'Second begin should have failed, but claims to be in a transaction.',
        db2.isInTransaction());
    assertTrue(
        'Second begin not reported as being locked',
        goog.gears.Database.isLockedException(e));
  }
  db1.commit();
  db2.begin();
  assertTrue('New second begin failed', db2.isInTransaction());
  db2.commit();
};


function testIsLockedExceptionMessage() {
  if (!database) {
    return;
  }

  var lockedExStr = 'Database operation failed. ERROR: database is locked ' +
      'DETAILS: database is locked';

  assertTrue(goog.gears.Database.isLockedException(lockedExStr));
  assertFalse(goog.gears.Database.isLockedException(
      'Database operation failed. ERROR: invalid SQL operation'));

  assertTrue(goog.gears.Database.isLockedException(Error(lockedExStr)));
}

function testNestedBegins() {
  if (!database) {
    return;
  }

  // Try normal nested begins.
  database.begin();
  try {
    database.begin();
    database.commit();
    database.commit();
  } catch (e) {
    fail('Could not run nested transactions');
  }

  // Now try nested begins with lower begin levels
  database.begin();
  try {
    database.beginDeferred();
    database.commit();
    database.commit();
  } catch (e) {
    fail('Could not run nested transactions');
  }

  // Now try begins with increasing begin levels. This should throw
  // an error.
  database.beginDeferred();
  try {
    database.beginImmediate();
    fail('Could not run nested transactions');
    database.commit();
    database.commit();
  } catch (e) {
    database.rollback(e);
  }
}


function testCommit() {
  if (!database) {
    return;
  }
  var db;
  try {
    db = new goog.gears.Database('tester', 'database-test');
  } catch (e) {
    debug('Could not create the database');
  }

  var committed = false;
  var rollbacked = false;

  database.begin();
  goog.events.listen(database, 'commit', function(e) {committed = true;});
  goog.events.listen(database, 'rollback', function(e) {rollbacked = true;});
  assertTrue('Incorrect reporting of being in a transaction',
             database.isInTransaction());
  database.execute('INSERT INTO Test VALUES(?, ?)', 'o', 'omega');
  var cnt = database.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 4, cnt);
  cnt = db.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 3, cnt);
  database.commit();
  cnt = db.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 4, cnt);

  assertFalse('Incorrect reporting of being in a transaction',
              database.isInTransaction());

  assertTrue('Commit event not dispatched', committed);
  assertFalse('Rollback event wrongly dispatched', rollbacked);
}

function testCommit2() {
  if (!database) {
    return;
  }
  var db;
  try {
    db = new goog.gears.Database('tester', 'database-test');
  } catch (e) {
    debug('Could not create the database');
  }

  var committed = false;
  var rollbacked = false;

  database.begin();
  goog.events.listen(database, 'commit', function(e) {committed = true;});
  goog.events.listen(database, 'rollback', function(e) {rollbacked = true;});
  database.execute('INSERT INTO Test VALUES(?, ?)', 's', 'sigma');
  assertTrue('Incorrect reporting of being in a transaction',
             database.isInTransaction());
  database.begin();
  assertTrue('Incorrect reporting of being in a transaction',
             database.isInTransaction());
  database.execute('INSERT INTO Test VALUES(?, ?)', 's', 'sigma');
  database.commit();

  assertFalse('Commit event wrongly dispatched', committed);
  assertFalse('Rollback event wrongly dispatched', rollbacked);

  assertTrue('Incorrect reporting of being in a transaction',
             database.isInTransaction());
  // the values should not be committed at this point.
  cnt = database.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 5, cnt);
  cnt = db.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 3, cnt);
  database.commit();
  cnt = db.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 5, cnt);
  assertFalse('Incorrect reporting of being in a transaction',
              database.isInTransaction());
  assertTrue('Commit function not called', committed);
  assertFalse('Rollback function wrongly called', rollbacked);
}

function testRollback() {
  if (!database) {
    return;
  }

  var begun = false;
  var committed = false;
  var rollbacked = false;

  goog.events.listen(database, 'begin', function(e) {begun = true;});
  database.begin();
  assertTrue('Begin event not dispatched', begun);
  goog.events.listen(database, 'commit', function(e) {committed = true;});
  goog.events.listen(database, 'rollback', function(e) {rollbacked = true;});
  database.execute('INSERT INTO Test VALUES(?, ?)', 'o', 'omega');
  var cnt = database.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 4, cnt);
  database.rollback();
  cnt = database.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 3, cnt);
  assertFalse('Incorrect reporting of being in a transaction',
              database.isInTransaction());
  assertFalse('Commit event wrongly dispatched', committed);
  assertTrue('Rollback event not dispatched', rollbacked);
}

function testRollback2() {
  if (!database) {
    return;
  }

  var begun = false;
  var committed = false;
  var rollbacked = false;

  database.begin();
  goog.events.listen(database, 'commit', function(e) {committed = true;});
  goog.events.listen(database, 'rollback', function(e) {rollbacked = true;});
  database.execute('INSERT INTO Test VALUES(?, ?)', 's', 'sigma');
  database.begin();
  database.execute('INSERT INTO Test VALUES(?, ?)', 's', 'sigma');
  database.rollback();
  // the values should not be rolledback at this point.
  cnt = database.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 5, cnt);
  assertFalse('Commit event wrongly dispatched', committed);
  assertFalse('Rollback event wrongly dispatched', rollbacked);

  database.rollback();
  cnt = database.queryValue('SELECT COUNT(*) FROM Test');
  assertEquals('Found wrong number of rows', 3, cnt);
  assertFalse('Incorrect reporting of being in a transaction',
              database.isInTransaction());
  assertFalse('Commit event wrongly dispatched', committed);
  assertTrue('Rollback event not dispatched', rollbacked);
}

function testPreventDefault() {
  if (!database) {
    return;
  }

  var committed = false;
  database.begin();
  goog.events.listen(database, 'beforecommit', function(e) {
    e.preventDefault();
  });
  goog.events.listen(database, 'commit', function(e) {committed = true;});
  database.execute('INSERT INTO Test VALUES(?, ?)', 'd', 'delta');
  database.commit();
  assertFalse('Commit ignored beforecommit preventDefault()', committed);
  database.rollback();
}

function testPreventDefault2() {
  if (!database) {
    return;
  }

  var rollbacked = false;
  database.begin();
  goog.events.listen(database, 'beforerollback', function(e) {
    e.preventDefault();
  });
  goog.events.listen(database, 'rollback', function(e) {
    rollbacked = true;
  });
  database.execute('INSERT INTO Test VALUES(?, ?)', 'd', 'delta');
  database.rollback();
  assertFalse('Rollback ignored beforerollback preventDefault()', rollbacked);
  goog.events.removeAll(database);
  database.rollback();
}

function testLastInsertId() {
  if (!database) {
    return;
  }
  database.execute('INSERT INTO Test VALUES (?, ?)', 'a', 'alpha');
  var id = database.getLastInsertRowId();
  database.execute('INSERT INTO Test VALUES (?, ?)', 'b', 'beta');
  assertEquals('Incorrect last insert id',
               id + 1, database.getLastInsertRowId());
}

function testError() {
  if (!database) {
    return;
  }

  var sql = 'INSERT INTO Unknown VALUES(?, ?)';

  try {
    database.execute('INSERT INTO Unknown VALUES(?, ?)', 'alpha', 1);
    fail('Should not have gotten here');
  } catch (e) {
    assertEquals('Wrong sql information', sql + ': ["alpha",1]', e.message0);
  }
}

function testThrowInBeforeSubmit() {
  if (!database) {
    return;
  }

  var errorSentinel = Error();

  var committed = false;
  database.begin();
  goog.events.listen(database, 'beforecommit', function(e) {
    throw errorSentinel;
  });
  goog.events.listen(database, 'commit', function(e) {
    committed = true;
  });
  database.execute('INSERT INTO Test VALUES(?, ?)', 'd', 'delta');
  try {
    database.commit();
    fail('Should have thrown an error');
  } catch (ex) {
    assertEquals(errorSentinel, ex);
    assertTrue('Should still be in the transaction',
               database.isInTransaction());
  }

  assertFalse('Should not have been commited since we threw an error ' +
              'in beforecommit', committed);
  database.rollback();
}

function testThrowInBeforeRollback() {
  if (!database) {
    return;
  }

  var errorSentinel = Error();

  var rollbacked = false;
  database.begin();
  goog.events.listen(database, 'beforerollback', function(e) {
    throw errorSentinel;
  });
  goog.events.listen(database, 'rollback', function(e) {
    rollbacked = true;
  });
  database.execute('INSERT INTO Test VALUES(?, ?)', 'd', 'delta');

  try {
    database.rollback();
    fail('Should have thrown an error');
  } catch (ex) {
    assertEquals(errorSentinel, ex);
    assertTrue('Should still be in the transaction',
               database.isInTransaction());
  }
  assertFalse('Should not have been rolled back since we threw an error ' +
              'in beforerollback', rollbacked);
  goog.events.removeAll(database);
  database.rollback();
}

</script>
</body>
</html>
